from django.db import connection

from app.model.cs_stock_goods import CsStockGoods
from app.my_exception import MyException
from app.service.goods import query_goods_by_id
from app.service.goods_stock import get_goods_stock_with_status
from libs.common import get_ts
from libs.db import DB


def handle_add_stock(cursor, order_id, shop_id, cashier_id, rows):
    now = get_ts()
    for row in rows:
        (sg_id, sg_goods_id, sg_cost_price, sg_amount, sg_subtotal) = row
        db_goods = query_goods_by_id(shop_id, sg_goods_id)
        db_goods_id = db_goods.get('id', 0)
        if db_goods_id <= 0:
            raise MyException('入库单存在无效的商品，商品ID"{}""'.format(sg_goods_id))
        else:
            db_goods_ver = db_goods.get('ver')
            db_goods_next_ver = db_goods_ver + 1
            db_goods_stock_prev = db_goods.get('scount')
            db_goods_stock_next = db_goods_stock_prev + sg_amount
            update_goods_sql = 'update table_goods set scount=%s,ver=%s where id=%s and ver=%s'
            bindings = [db_goods_stock_next, db_goods_next_ver, db_goods_id, db_goods_ver]
            affected = cursor.execute(update_goods_sql, bindings)
            if affected < 1:
                raise MyException('更新商品库存失败，商品ID"{}"'.format(db_goods_id))
            stock_typ = 1
            insert_stock_log_sql = 'insert into cs_stock_logs (stock_order_id,stock_typ,goods_id,shop_id,cashier_id,cost_price,amount,amount_prev,amount_next,created) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
            bindings = [order_id, stock_typ, db_goods_id, shop_id, cashier_id, sg_cost_price, sg_amount,
                        db_goods_stock_prev, db_goods_stock_next, now]
            affected = cursor.execute(insert_stock_log_sql, bindings)
            if affected < 1:
                raise MyException('添加商品入库日志失败，商品ID"{}"'.format(db_goods_id))
    update_sql = 'update cs_stock_goods set order_id=%s,status=100 where shop_id=%s and status=0'
    bindings = [order_id, shop_id]
    cursor.execute(update_sql, bindings)


def save_goods_to_stock(shop_id, cashier_id, goods_id, db_goods_data, input_amount=None):
    err = None
    res = {
        'stock_id': 0,
        'opt_typ': '',
        'amount': 0,
        'subtotal': 0,
        'cost_price': 0,
    }
    try:
        db_goods_stock = get_goods_stock_with_status(shop_id, cashier_id, goods_id)
        db_goods_stock_id = db_goods_stock.get('id', 0)
        now = get_ts()
        cost_price = db_goods_data.get('cost_price', 0)
        res['cost_price'] = cost_price
        if db_goods_stock_id == 0:
            res['opt_typ'] = 'add'
            if input_amount is None or input_amount < 1:
                input_amount = 1
            subtotal = cost_price * input_amount
            cs_stock_goods = CsStockGoods()
            cs_stock_goods.shop_id = shop_id
            cs_stock_goods.cashier_id = cashier_id
            cs_stock_goods.goods_id = goods_id
            cs_stock_goods.cost_price = cost_price
            cs_stock_goods.amount = input_amount
            cs_stock_goods.subtotal = subtotal
            cs_stock_goods.status = 0
            cs_stock_goods.created = now
            cs_stock_goods.updated = now
            cs_stock_goods.save()
            res['stock_id'] = cs_stock_goods.id
            res['subtotal'] = subtotal
            res['amount'] = input_amount
        else:
            res['opt_typ'] = 'update'
            res['stock_id'] = db_goods_stock_id
            if input_amount is None or input_amount < 1:
                db_goods_stock_amount = db_goods_stock.get('amount', 0)
                db_goods_stock_amount_next = db_goods_stock_amount + 1
            else:
                db_goods_stock_amount_next = input_amount
            subtotal = cost_price * db_goods_stock_amount_next
            new_stock_data = {
                'amount': db_goods_stock_amount_next,
                'cost_price': cost_price,
                'subtotal': subtotal,
                'updated': now,
            }
            CsStockGoods.objects.filter(id=db_goods_stock_id).update(**new_stock_data)
            res['subtotal'] = subtotal
            res['amount'] = db_goods_stock_amount_next
    except Exception as e:
        err = '保存库存异常'
        print(err, e)
    return res, err


def del_goods_from_stock(shop_id, cashier_id, goods_id):
    err = None
    try:
        CsStockGoods.objects.filter(shop_id=shop_id, cashier_id=cashier_id, goods_id=goods_id, status=0).delete()
    except Exception as e:
        err = '删除库存异常'
        print(err, e, type(e))
    return err


def add_up_stock_info(shop_id, cashier_id):
    sql = 'select sum(amount) total_amount,sum(subtotal) total_price' \
          ' from cs_stock_goods where shop_id=%s and cashier_id=%s and `status`=0'
    data = {'total_amount': 0, 'total_price': 0}
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, [shop_id, cashier_id])
            row = cursor.fetchone()
            if row is not None:
                [total_amount, total_price] = row
                data = {'total_amount': total_amount, 'total_price': total_price}
    except Exception as e:
        print('统计库存异常', e)
    return data


# 更新商品库存 stock_order_id = {'stock_order_id', 'stock_typ', 'shop_id', 'cashier_id', 'cash_device_code'}
def change_goods_stock(cursor, goods_id, stock_change_num: int, stock_log_data: dict):
    db = DB('table_goods', conn=cursor)
    db_goods_data = db.one(['id', 'scount', 'sell_count', 'ver'], {'id': goods_id})
    db_goods_id = db_goods_data.get('id')
    db_goods_sell_count = db_goods_data.get('sell_count')
    db_goods_ver = db_goods_data.get('ver')
    db_goods_before = db_goods_data.get('scount')
    db_goods_after = db_goods_before + stock_change_num
    new_goods_sell_count = db_goods_sell_count - stock_change_num
    if db_goods_id == 0:
        err = '商品ID({})不存在'.format(goods_id)
        return None, err
    else:
        now = get_ts()
        update_goods_data = {
            'scount': db_goods_after,
            'sell_count': new_goods_sell_count,
            'ver': db_goods_ver + 1,
            'updated': now,
        }
        update_goods_where = {
            'id': goods_id,
        }
        affected, err = db.update(update_goods_data, update_goods_where)
        if err is not None:
            return None, err
        db = DB('cs_stock_logs', conn=cursor)
        stock_log_data.update({
            'goods_id': goods_id,
            'amount': stock_change_num,
            'amount_prev': db_goods_before,
            'amount_next': db_goods_after,
            'created': now,
        })
        affected, err = db.insert(stock_log_data)
        if err is not None:
            return None, err
        if affected == 0:
            err = '库存流水保存失败'
            return None, err
    return stock_log_data, err
